"""
To use the code, either execute it in a Python kernel (e.g. SageMath) or launch kernel under the same directoy of this file and do:

sage: from csv_to_sql import *

This defines the function

    create_sql_with_schema(save_name, schema_names, data_dir, csv_folder)

Then define the schema_names as a dictionary of the format 

    { 'table name': [ CSV files to use ] }

Then pass it over to execute create_sql_with_schema to create an SQLite database
that will be stored as the file under 

    data_dir + save_name

For the databases to be readable by Vn_sql.py (or VnSQL), the table names have to be 'Vn' where n is the number in Vn. 

For example, the following code creates an SQLite database file named V-database_3-16c.db under /Users/, 
containing all data in the CSV files listed in the schema_names stored under /Users/V-csv/
    
sage: schema_names = {
        'V1' : ['V1-data_3-15c.csv', 'V1-data_16c.csv'],
        'V2' : ['V2-data_3-15c.csv', 'V2-data_16c_part-1.csv', 'V2-data_16c_part-2.csv'],
        'V3' : ['V3-data_3-14c.csv', 'V3-data_V2-equiv_15-16c.csv'],
        'V4' : ['V4-data_3-14c.csv', 'V4-data_V2-equiv_15-16c.csv']
    }
sage: creat_sql_with_schema('V-database_3-16c.db', schema_names = schema_names, 
    data_dir = "/Users/, csv_folder = "/Users/V-csv/")

>>  V1-data_3-15c.csv successfully inserted under table V1
    V1-data_16c.csv successfully inserted under table V1
    V2-data_3-15c.csv successfully inserted under table V2
    V2-data_16c_part-1.csv successfully inserted under table V2
    V2-data_16c_part-2.csv successfully inserted under table V2
    V3-data_3-14c.csv successfully inserted under table V3
    V3-data_V2-equiv_15-16c.csv successfully inserted under table V3
    V4-data_3-14c.csv successfully inserted under table V4
    V4-data_V2-equiv_15-16c.csv successfully inserted under table V4
    Index created for table V1 on column 'KnotTheory Name' and `SnapPy Name` 
    Index created for table V2 on column 'KnotTheory Name' and `SnapPy Name` 
    Index created for table V3 on column 'KnotTheory Name' and `SnapPy Name` 
    Index created for table V4 on column 'KnotTheory Name' and `SnapPy Name` 
    V-database_3-16c.db successfully created and connection closed
"""


import sqlite3
import pandas as pd

def creat_sql_with_schema(save_name, schema_names, data_dir, csv_dir):
    con = sqlite3.connect(data_dir + save_name)

    for table_name in schema_names.keys():
        for csv_name in schema_names[table_name]:
            try:
                pd.read_csv(csv_dir + csv_name).to_sql(table_name, con, index = False, if_exists='append')

                print(f'{csv_name} successfully inserted under table {table_name}')
            except:
                print(f'error happened when inserting {csv_name} under table {table_name}')

    for table_name in schema_names.keys():
        try:
            with con:
                con.execute(f"CREATE INDEX idx_{table_name}_knot_theory_name ON {table_name} (`KnotTheory Name`)")
                con.execute(f"CREATE INDEX idx_{table_name}_snappy_name ON {table_name} (`SnapPy Name`)")
                
            print(f"Index created for table {table_name} on column 'KnotTheory Name' and `SnapPy Name` ")
        except sqlite3.OperationalError as e:
            print(f"Could not create index for table {table_name}: {e}")

    con.close()


    print(f'{save_name} successfully created and connection closed')